﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace PstBakup2013Server
{
    internal class SqlHelper
    {
        private static SqlHelper _instance;
        private SqlConnection _sqlConnection = new SqlConnection();

        private SqlHelper()
        {
            this.ServerName = String.Empty;
            this.DataBaseName = String.Empty;
            this.Login = String.Empty;
            this.Password = String.Empty;
            this.flushLogTimer.Elapsed += new System.Timers.ElapsedEventHandler(this.FlushLog);
            this.flushLogTimer.Start();
        }

        ~SqlHelper()
        {
            this.flushLogTimer.Stop();
            this.FlushLog(null, null);
            this.flushLogTimer.Stop();
        }

        internal static SqlHelper GetInstance()
        {
            if (_instance == null)
                _instance = new SqlHelper();
            return _instance;
        }

        /// <summary>
        /// Get or Set the name of the SQL server.
        /// </summary>
        internal string ServerName { get; set; }

        /// <summary>
        /// Get or Set the name of the database
        /// </summary>
        internal string DataBaseName { get; set; }

        /// <summary>
        /// Login used to connect to the database
        /// </summary>
        internal string Login { get; set; }

        /// <summary>
        /// Password used to connect to the database
        /// </summary>
        internal string Password { get; set; }

        /// <summary>
        /// Gets if the connection with the Sql server is Open.
        /// </summary>
        internal bool IsConnected
        {
            get { return this._sqlConnection.State == System.Data.ConnectionState.Open; }
        }

        /// <summary>
        /// Connect to the SQL Database.
        /// </summary>
        /// <param name="login">Username that is allows to connect to this SQL server. Use String.Empty if no login is required.</param>
        /// <param name="password">Password of the user. Use String.Empty if no password is required.</param>
        /// <returns>True if the connection succeed, false if an error occurs.</returns>
        internal bool Connect(String login, String password)
        {
            if (login != null)
                this.Login = login;
            if (password != null)
                this.Password = password;

            return this.Connect();
        }

        /// <summary>
        /// Connect to the SQL Database. Use <see cref="Login"/> and <see cref="Password"/> as credentials.
        /// </summary>
        /// <returns>True if the connection succeed, false if an error occurs.</returns>
        internal bool Connect()
        {
            try
            {
                if (this.IsConnected)
                    _sqlConnection.Close();

                if (!string.IsNullOrEmpty(this.Login) && !string.IsNullOrEmpty(this.Password))
                {
                    this.WriteLog("Connecting to : " + DataBaseName + " on : " + ServerName + " with credentials : " + this.Login);
                    _sqlConnection.ConnectionString = string.Format("Server={0};Database={1};User id={2};Password={3};", ServerName, DataBaseName, this.Login, this.Password);
                }
                else
                {
                    this.WriteLog("Connecting to : " + DataBaseName + " on : " + ServerName + " with current credentials.");
                    _sqlConnection.ConnectionString = string.Format("Server={0};Database={1};Integrated Security=sspi;", ServerName, DataBaseName);
                }
                _sqlConnection.Open();
                return true;
            }
            catch (Exception ex)
            {
                this.WriteLog("**** Failed to connect to database. " + ex.Message);
            }

            return false;
        }

        /// <summary>
        /// Close the connection with the server.
        /// </summary>
        internal void Disconnect()
        {
            try
            {
                if (this.IsConnected)
                    _sqlConnection.Close();

                this.WriteLog("Disconnected from the database.");
            }
            catch (Exception ex)
            {
                this.WriteLog("**** Error while trying to disconnect from the database. " + ex.Message);
            }
        }

        /// <summary>
        /// Execute a command against the Sql Server.
        /// </summary>
        /// <param name="sqlCommand">The command to run against the SQL Server.</param>
        /// <returns>True if no Exception have been thrown, otherwise false.</returns>
        internal bool ExecuteCommand(String sqlCommand)
        {
            if (this.IsConnected || this.Connect(this.Login, this.Password))
                using (SqlCommand command = new SqlCommand())
                {
                    try
                    {
                        this.WriteLog("Executing command : " + sqlCommand);

                        command.Connection = _sqlConnection;
                        command.CommandText = sqlCommand;
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        this.WriteLog("**** Error while executing command. " + ex.Message);
                        return false;
                    }
                }
            return true;
        }

        /// <summary>
        /// Query the database with the provided 'SELECT' command and return the result of thi query as an array of objects.
        /// </summary>
        /// <param name="selectCommand">SELECT sql query to execute.</param>
        /// <returns>An array of Objects with the result of the query.</returns>
        internal string ExecuteSelectQuery(string selectCommand, ref bool isSuccessfull)
        {
            string result = String.Empty;
            isSuccessfull = false;

            if (this.IsConnected || this.Connect(this.Login, this.Password))
                using (SqlCommand command = new SqlCommand())
                {
                    SqlDataReader dataReader = null;
                    try
                    {
                        this.WriteLog("Query the database with the SELECT command : " + selectCommand);
                        command.Connection = _sqlConnection;

                        command.CommandText = selectCommand;
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                for (int i = 0; i < dataReader.FieldCount; i++)
                                {
                                    result += dataReader.GetValue(i).ToString() + ";";
                                }
                                result = result.Substring(0, result.Length - 1);
                                result += "\r\n";
                            }
                            result = result.Substring(0, result.Length - 2);
                            isSuccessfull = true;
                            dataReader.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        this.WriteLog("**** Error while querying the database : " + ex.Message);
                    }
                    finally
                    {
                        if (dataReader != null && !dataReader.IsClosed)
                            dataReader.Close();
                    }
                }

            return result;
        }

        /// <summary>
        /// Get a CSV Formatted list of ResultID with their meaning.
        /// </summary>
        /// <returns>A CSV Formatted list of ResultID with their meaning.</returns>
        internal string GetResultIDs()
        {
            string result = String.Empty;

            if (this.IsConnected || this.Connect(this.Login, this.Password))
                using (SqlCommand command = new SqlCommand())
                {
                    SqlDataReader dataReader = null;
                    try
                    {
                        this.WriteLog("Getting list of ResultIDs");
                        command.Connection = _sqlConnection;

                        command.CommandText = "SELECT * FROM [" + this.DataBaseName + "].[dbo].[tbResultLabel]";
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                int resultID = dataReader.GetInt32(0);
                                string label = dataReader.GetString(1);
                                result += String.Format("{0};{1}\r\n", resultID, label);
                            }
                            result = result.Substring(0, result.Length - 2);
                            dataReader.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        this.WriteLog("**** Error while getting list of ResultIDs : " + ex.Message);
                    }
                    finally
                    {
                        if (dataReader != null && !dataReader.IsClosed)
                            dataReader.Close();
                    }
                }

            return result;
        }

        /// <summary>
        /// Get a list of all registered clients
        /// </summary>
        /// <returns>A CSV foratted list of all clients. Format : [ClientID;ComputerName;UserName;LastContactDate;ClientVersion]</returns>
        internal string GetAllClientsList()
        {
            string result = String.Empty;
            Dictionary<String, Int32> pstFilesCount = this.GetPstCountForAllClients();

            if (this.IsConnected || this.Connect(this.Login, this.Password))
                using (SqlCommand command = new SqlCommand())
                {
                    SqlDataReader dataReader = null;
                    try
                    {
                        this.WriteLog("Getting list of All Clients");
                        command.Connection = _sqlConnection;

                        command.CommandText = "SELECT * FROM [" + this.DataBaseName + "].[dbo].[tbClients]";
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                string clientID = dataReader.GetGuid(0).ToString();
                                string computerName = dataReader.GetString(1);
                                string userName = dataReader.GetString(2);
                                DateTime lastContactDate = dataReader.GetDateTime(3).ToLocalTime();
                                string clientVersion = dataReader.GetString(4);
                                result += String.Format("{0};{1};{2};{3};{4};{5}\r\n", clientID, computerName, userName, lastContactDate, clientVersion, pstFilesCount.ContainsKey(clientID) ? pstFilesCount[clientID] : 0);
                            }
                            result = result.Substring(0, result.Length - 2);
                            dataReader.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        this.WriteLog("**** Error while getting list of All Clients : " + ex.Message);
                    }
                    finally
                    {
                        if (dataReader != null && !dataReader.IsClosed)
                            dataReader.Close();
                    }
                }

            return result;
        }

        /// <summary>
        /// Get a list of all registered clients with a specific version.
        /// </summary>
        /// <param name="version">The version for which we need to retrieve the clients. Use '%' for getting all clients, whatever the version.</param>
        /// <returns>A CSV formatted list of all clients with this version. Format : [ClientID;ComputerName;UserName;LastContactDate;ClientVersion]</returns>
        internal string GetAllClientsForVersion(string version)
        {
            string result = String.Empty;
            Dictionary<String, Int32> pstFilesCount = this.GetPstCountForAllClients();

            if (this.IsConnected || this.Connect(this.Login, this.Password))
                using (SqlCommand command = new SqlCommand())
                {
                    SqlDataReader dataReader = null;
                    try
                    {
                        this.WriteLog("Getting list of All Clients with version : " + version);
                        command.Connection = _sqlConnection;

                        command.CommandText = "SELECT * FROM [" + this.DataBaseName + "].[dbo].[tbClients] WHERE [ClientVersion] like '" + version + "'";
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                string clientID = dataReader.GetGuid(0).ToString();
                                string computerName = dataReader.GetString(1);
                                string userName = dataReader.GetString(2);
                                DateTime lastContactDate = dataReader.GetDateTime(3).ToLocalTime();
                                string clientVersion = dataReader.GetString(4);
                                result += String.Format("{0};{1};{2};{3};{4};{5}\r\n", clientID, computerName, userName, lastContactDate, clientVersion, pstFilesCount.ContainsKey(clientID) ? pstFilesCount[clientID] : 0);
                            }
                            result = result.Substring(0, result.Length - 2);
                            dataReader.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        this.WriteLog("**** Error while getting list of All Clients with version : " + version + "\r\n" + ex.Message);
                    }
                    finally
                    {
                        if (dataReader != null && !dataReader.IsClosed)
                            dataReader.Close();
                    }
                }
            return result;
        }

        /// <summary>
        /// Get a CSV formatted list of clients with a specific destination
        /// </summary>
        /// <param name="destination">Destination to search for. Example : 192.168.0.250. Use '%' to get all the clients, whatever the destination.</param>
        /// <returns>A CSV formatted list of clients with this destination.</returns>
        internal string GetAllClientsForDestination(string destination)
        {
            string result = String.Empty;
            Dictionary<String, Int32> pstFilesCount = this.GetPstCountForAllClients(destination);
            List<String> clients = new List<string>();

            if (this.IsConnected || this.Connect(this.Login, this.Password))
                using (SqlCommand command = new SqlCommand())
                {
                    SqlDataReader dataReader = null;
                    try
                    {
                        this.WriteLog("Getting list of All Clients with destination : " + destination);
                        command.Connection = _sqlConnection;

                        command.CommandText = @"SELECT * FROM [" + this.DataBaseName + "].[dbo].[tbClients] JOIN [" + this.DataBaseName + @"].[dbo].[tbPstFiles] ON tbPstFiles.ClientID = tbClients.ClientID AND tbPstFiles.RemotePath LIKE '\\" + destination + "%'";
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                string clientID = dataReader.GetGuid(0).ToString();
                                string computerName = dataReader.GetString(1);
                                string userName = dataReader.GetString(2);
                                DateTime lastContactDate = dataReader.GetDateTime(3).ToLocalTime();
                                string clientVersion = dataReader.GetString(4);
                                if (!clients.Contains(clientID))
                                {
                                    result += String.Format("{0};{1};{2};{3};{4};{5}\r\n", clientID, computerName, userName, lastContactDate, clientVersion, pstFilesCount.ContainsKey(clientID) ? pstFilesCount[clientID] : 0);
                                    clients.Add(clientID);
                                }
                            }
                            result = result.Substring(0, result.Length - 2);
                            dataReader.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        this.WriteLog("**** Error while getting list of All Clients with destination : " + destination + "\r\n" + ex.Message);
                    }
                    finally
                    {
                        if (dataReader != null && !dataReader.IsClosed)
                            dataReader.Close();
                    }
                }
            return result;
        }

        /// <summary>
        /// Get the list of all Pst File for a specific client.
        /// </summary>
        /// <param name="clientID">Guid of the client.</param>
        /// <returns>A list of <see cref="PstFile"/></returns>
        internal List<PstFile> GetAllPstFilesForClient(string clientID)
        {
            List<PstFile> pstFiles = new List<PstFile>();

            if (this.IsConnected || this.Connect(this.Login, this.Password))
                using (SqlCommand command = new SqlCommand())
                {
                    SqlDataReader dataReader = null;
                    try
                    {
                        this.WriteLog("Getting list of all PstFile for  client : " + clientID);
                        command.Connection = _sqlConnection;

                        command.CommandText = "SELECT * FROM [" + this.DataBaseName + "].[dbo].[tbPstFiles] WHERE [ClientID] LIKE '" + clientID + "'";
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                PstFile pstFile = new PstFile();
                                pstFile.LocalPath = dataReader.GetString(1);
                                pstFile.RemotePath = dataReader.GetString(2);
                                pstFile.IsSetToBackup = dataReader.GetBoolean(3);
                                pstFile.IsScheduledForBackup = dataReader.GetBoolean(4);
                                pstFile.IsSavedAtLeastOnce = dataReader.GetBoolean(5);
                                pstFile.LastDateSaved = dataReader.GetDateTime(6).ToLocalTime();
                                pstFile.Size = dataReader.GetInt64(7);
                                pstFile.CompressedSize = dataReader.GetInt64(8);
                                pstFile.IsCompressed = dataReader.GetBoolean(9);
                                pstFile.StartTime = dataReader.GetDateTime(10).ToLocalTime();
                                pstFile.EndTime = dataReader.GetDateTime(11).ToLocalTime();
                                pstFile.ResultID = dataReader.GetInt32(12);
                                pstFile.ErrorMessage = dataReader.GetString(13);

                                pstFiles.Add(pstFile);
                            }
                            dataReader.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        this.WriteLog("**** Error while getting list of All PstFile for  client : " + ex.Message);
                    }
                    finally
                    {
                        if (dataReader != null && !dataReader.IsClosed)
                            dataReader.Close();
                    }
                }

            return pstFiles;
        }

        /// <summary>
        /// Get the number of PST Files that have been registered in the database for all clients.
        /// </summary>
        /// <returns>A list of ClientID with number of PST files.</returns>
        internal Dictionary<String, Int32> GetPstCountForAllClients()
        {
            Dictionary<String, Int32> result = new Dictionary<string, int>();

            if (this.IsConnected || this.Connect(this.Login, this.Password))
                using (SqlCommand command = new SqlCommand())
                {
                    SqlDataReader dataReader = null;
                    try
                    {
                        this.WriteLog("Getting number of PST Files for all Clients");
                        command.Connection = _sqlConnection;

                        command.CommandText = "SELECT [" + this.DataBaseName + "].[dbo].[tbPstFiles].ClientID, COUNT(*) FROM [" + this.DataBaseName + "].[dbo].[tbPstFiles] GROUP By [" + this.DataBaseName + "].[dbo].[tbPstFiles].ClientID";
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                string clientID = dataReader.GetGuid(0).ToString();
                                int count = dataReader.GetInt32(1);
                                result.Add(clientID, count);
                            }
                            dataReader.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        this.WriteLog("**** Error while getting number of PST Files all Clients.\r\n" + ex.Message);
                    }
                    finally
                    {
                        if (dataReader != null && !dataReader.IsClosed)
                            dataReader.Close();
                    }
                }
            return result;
        }

        /// <summary>
        /// Get the number of PST Files that have been registered in the database for all clients and with a specific destination.
        /// </summary>
        /// <param name="destination">Filter on destination</param>
        /// <returns>A list of ClientID with number of PST files.</returns>
        internal Dictionary<String, Int32> GetPstCountForAllClients(string destination)
        {
            Dictionary<String, Int32> result = new Dictionary<string, int>();

            if (this.IsConnected || this.Connect(this.Login, this.Password))
                using (SqlCommand command = new SqlCommand())
                {
                    SqlDataReader dataReader = null;
                    try
                    {
                        this.WriteLog("Getting number of PST Files for all Clients with destination : " + destination);
                        command.Connection = _sqlConnection;

                        command.CommandText = @"SELECT [" + this.DataBaseName + "].[dbo].[tbPstFiles].ClientID, COUNT(*) FROM [" + this.DataBaseName + @"].[dbo].[tbPstFiles] WHERE [RemotePath] LIKE '\\" + destination + "%' GROUP By [" + this.DataBaseName + "].[dbo].[tbPstFiles].ClientID";
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                string clientID = dataReader.GetGuid(0).ToString();
                                int count = dataReader.GetInt32(1);
                                result.Add(clientID, count);
                            }
                            dataReader.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        this.WriteLog("**** Error while getting number of PST Files all Clients with destination : " + destination + "\r\n" + ex.Message);
                    }
                    finally
                    {
                        if (dataReader != null && !dataReader.IsClosed)
                            dataReader.Close();
                    }
                }
            return result;
        }

        /// <summary>
        /// Get a list of unique destinations
        /// </summary>
        /// <returns>A List of unique destinations.</returns>
        internal List<String> GetAllDestinations()
        {
            List<String> destinations = new List<String>();

            if (this.IsConnected || this.Connect(this.Login, this.Password))
                using (SqlCommand command = new SqlCommand())
                {
                    SqlDataReader dataReader = null;
                    try
                    {
                        this.WriteLog("Getting list of all destinations");
                        command.Connection = _sqlConnection;

                        command.CommandText = "SELECT [RemotePath] FROM [" + this.DataBaseName + "].[dbo].[tbPstFiles]";
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                string destination = this.GetRootPath(dataReader.GetString(0));
                                if (!destinations.Contains(destination))
                                    destinations.Add(destination);
                            }
                            dataReader.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        this.WriteLog("**** Error while getting list of destinations : " + ex.Message);
                    }
                    finally
                    {
                        if (dataReader != null && !dataReader.IsClosed)
                            dataReader.Close();
                    }
                }

            return destinations;
        }

        /// <summary>
        /// Get a list of unique Versions
        /// </summary>
        /// <returns>A List of unique Verions.</returns>
        internal List<String> GetAllVersions()
        {
            List<String> versions = new List<String>();

            if (this.IsConnected || this.Connect(this.Login, this.Password))
                using (SqlCommand command = new SqlCommand())
                {
                    SqlDataReader dataReader = null;
                    try
                    {
                        this.WriteLog("Getting list of all Versions");
                        command.Connection = _sqlConnection;

                        command.CommandText = "SELECT [ClientVersion] FROM [" + this.DataBaseName + "].[dbo].[tbClients]";
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                string version = dataReader.GetString(0);
                                if (!versions.Contains(version))
                                    versions.Add(version);
                            }
                            dataReader.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        this.WriteLog("**** Error while getting list of versions : " + ex.Message);
                    }
                    finally
                    {
                        if (dataReader != null && !dataReader.IsClosed)
                            dataReader.Close();
                    }
                }

            return versions;
        }

        /// <summary>
        /// Get the root path of a destination. e.g: for "\\192.168.0.250\Pst Files\Courtel" will return "192.168.0.250"
        /// </summary>
        /// <param name="fullPath">Full path to shorten</param>
        /// <returns>The root path</returns>
        private string GetRootPath(string fullPath)
        {
            string result = String.Empty;

            try
            {
                if (fullPath.StartsWith(@"\\"))
                {
                    fullPath = fullPath.Substring(2);
                    int index = fullPath.IndexOf(@"\");

                    if (index != -1)
                    {
                        result = fullPath.Substring(0, index);
                    }
                }
            }
            catch (Exception) { }

            return result;
        }

        /// <summary>
        /// Delete all Clients regitered in the database.
        /// </summary>
        internal void DeleteAllClients()
        {
            this.ExecuteCommand("DELETE FROM [" + this.DataBaseName + "].[dbo].[tbClients]");
        }

        /// <summary>
        /// Delete All Pst Files registered in the database.
        /// </summary>
        internal void DeleteAllPstFiles()
        {
            this.ExecuteCommand("DELETE FROM [" + this.DataBaseName + "].[dbo].[tbPstFiles]");
        }

        /// <summary>
        /// Register a nex client in the database.
        /// </summary>
        /// <param name="clientId">GUID of the new client.</param>
        /// <param name="computerName">Name of the computer for this new client.</param>
        /// <param name="userName">Name of the user that have open the Windows session.</param>
        /// <param name="sentDate">Date when the message have been generating.</param>
        /// <param name="clientVersion">Version of the Pst Backup 2013 Agent.</param>
        /// <returns>True no Exception have been thrown, otherwise false.</returns>
        internal bool RegisterNewClient(String clientId, String computerName, String userName, DateTime sentDate, Version clientVersion)
        {
            computerName = computerName.Substring(0, System.Math.Min(100, computerName.Length));
            userName = userName.Substring(0, System.Math.Min(100, userName.Length));

            this.WriteLog("Registering new client : " + string.Format("'{0}', '{1}', '{2}', '{3}', '{4}'", new Guid(clientId), computerName, userName, sentDate, clientVersion));
            return this.ExecuteCommand("INSERT INTO [" + this.DataBaseName + "].[dbo].[tbClients] VALUES (" + string.Format("'{0}', '{1}', '{2}', '{3}', '{4}'", new Guid(clientId), computerName, userName, sentDate, clientVersion) + ")");
        }

        /// <summary>
        /// Check if there is already a record in the database which have these clientID and localPath.
        /// </summary>
        /// <param name="clientID">ClientID to search for.</param>
        /// <param name="localPath">LocalPath to search for.</param>
        /// <returns>True if record have been found, otherwise false.</returns>
        internal bool PstFileExists(String clientID, String localPath)
        {
            if (this.IsConnected || this.Connect(this.Login, this.Password))
                using (SqlCommand command = new SqlCommand())
                {
                    SqlDataReader dataReader = null;
                    try
                    {
                        this.WriteLog(String.Format("Checking if PstFile exists : {0} \t {1}", clientID, localPath));
                        command.Connection = _sqlConnection;

                        command.CommandText = "SELECT [ClientID], [LocalPath] FROM [" + this.DataBaseName + "].[dbo].[tbPstFiles] WHERE [ClientID] LIKE '" + clientID + "' and [LocalPath] LIKE '" + localPath + "'";
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                this.WriteLog(String.Format("{0} \t {1} has been found.", clientID, localPath));
                                return true;
                            }
                            dataReader.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        this.WriteLog("**** Error while checking for pstFile exists : " + ex.Message);
                    }
                    finally
                    {
                        if (dataReader != null && !dataReader.IsClosed)
                            dataReader.Close();
                    }
                }

            this.WriteLog(String.Format("{0} \t {1} has not been found.", clientID, localPath));
            return false;
        }

        /// <summary>
        /// Check if a client is already record in the database.
        /// </summary>
        /// <param name="clientID">ClientId to search for.</param>
        /// <returns>Ture if the ClientID have been found, otherwise false.</returns>
        internal bool ClientExists(String clientID)
        {
            if (this.IsConnected || this.Connect(this.Login, this.Password))
                using (SqlCommand command = new SqlCommand())
                {
                    SqlDataReader dataReader = null;
                    try
                    {
                        this.WriteLog(String.Format("Checking if Client {0} exists.", clientID));
                        command.Connection = _sqlConnection;

                        command.CommandText = "SELECT [ClientID] FROM [" + this.DataBaseName + "].[dbo].[tbClients] WHERE [ClientID] LIKE '" + clientID + "'";
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                this.WriteLog(String.Format("{0} has been found.", clientID));
                                return true;
                            }
                            dataReader.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        this.WriteLog("**** Error while searching if client exists. " + ex.Message);
                    }
                    finally
                    {
                        if (dataReader != null && !dataReader.IsClosed)
                            dataReader.Close();
                    }
                }

            this.WriteLog(String.Format("{0} has not been found.", clientID));
            return false;
        }

        /// <summary>
        /// Register a new PSTfile into the database.
        /// </summary>
        /// <param name="clientID">ClientID that have sent the message.</param>
        /// <param name="pstFileToRegister">PST File to register into the database</param>
        /// <returns>True if no exception have been thrown, otherwise false.</returns>
        internal bool RegisterNewPstFile(String clientID, PstFile pstFileToRegister)
        {
            object lastDateSaved = DBNull.Value;
            if (pstFileToRegister.ResultID == 00)
                lastDateSaved = pstFileToRegister.EndTime;

            object[] values = new object[] 
            {
                new Guid(clientID), 
                pstFileToRegister.LocalPath.ToLower().Substring(0,System.Math.Min(254,pstFileToRegister.LocalPath.Length)), 
                pstFileToRegister.RemotePath.ToLower().Substring(0,System.Math.Min(254,pstFileToRegister.RemotePath.Length)), 
                pstFileToRegister.IsSetToBackup, 
                pstFileToRegister.IsScheduledForBackup, 
                pstFileToRegister.ResultID==00,
                lastDateSaved,
                pstFileToRegister.Size,
                pstFileToRegister.CompressedSize,
                pstFileToRegister.IsCompressed,
                pstFileToRegister.StartTime,
                pstFileToRegister.EndTime,
                pstFileToRegister.ResultID,
                pstFileToRegister.ErrorMessage.Substring(0,System.Math.Min(254,pstFileToRegister.ErrorMessage.Length))            
            };

            this.WriteLog(String.Format("Registering new PST File : '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}'", values));

            return this.ExecuteCommand("INSERT INTO [" + this.DataBaseName + "].[dbo].[tbPstFiles] VALUES (" + string.Format("'{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}'", values) + ")");
        }

        /// <summary>
        /// Get the date of the last successfull backup for this PST File of this client.
        /// </summary>
        /// <param name="clientID">Id of the client.</param>
        /// <param name="localPath">Local path of the PST file.</param>
        /// <returns>The date of the last succesfull backup.</returns>
        internal DateTime GetLastDateSaved(String clientID, String localPath)
        {
            DateTime lastDateSaved = new DateTime();

            if (this.IsConnected || this.Connect(this.Login, this.Password))
                using (SqlCommand command = new SqlCommand())
                {
                    SqlDataReader dataReader = null;
                    try
                    {
                        this.WriteLog(String.Format("Get LastDateSaved for {0} \t {1}.", clientID, localPath));
                        command.Connection = _sqlConnection;

                        command.CommandText = String.Format("SELECT [LastDateSaved] FROM [" + this.DataBaseName + "].[dbo].[tbPstFiles] WHERE [ClientID] LIKE '{0}' AND [LocalPath] LIKE '{1}'", clientID, localPath.ToLower());
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                lastDateSaved = dataReader.GetDateTime(0);
                            }
                            dataReader.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        this.WriteLog("**** Error while getting last DateSaved : " + ex.Message);
                    }
                    finally
                    {
                        if (dataReader != null && !dataReader.IsClosed)
                            dataReader.Close();
                    }
                }

            this.WriteLog(lastDateSaved.ToString());
            return lastDateSaved;
        }

        /// <summary>
        /// Update the PstFile record with new values.
        /// </summary>
        /// <param name="clientID">ClientID for which we need to update values.</param>
        /// <param name="pstFile">PstFile that contains new values.</param>
        /// <returns></returns>
        internal bool UpdatePstFileRecord(String clientID, PstFile pstFile, DateTime lastDateSaved)
        {
            object[] values = new object[] 
            {
            pstFile.RemotePath.ToLower().Substring(0,System.Math.Min(254,pstFile.RemotePath.Length)), 
            pstFile.IsSetToBackup,
            pstFile.IsScheduledForBackup,
            lastDateSaved,
            pstFile.Size,
            pstFile.CompressedSize,
            pstFile.IsCompressed,
            pstFile.StartTime,
            pstFile.EndTime,
            pstFile.ResultID,
            pstFile.ErrorMessage.Substring(0,System.Math.Min(254,pstFile.ErrorMessage.Length))
            };

            this.WriteLog(String.Format("Updating PstFile Record for : {0} \t {1}", clientID, pstFile.LocalPath));

            if (pstFile.ResultID == 00)
                return this.ExecuteCommand(String.Format("UPDATE [" + this.DataBaseName + "].[dbo].[tbPstFiles] SET RemotePath = '{0}', IsSetToBackup = '{1}', IsScheduledForBackup = '{2}', IsSavedAtLeastOnce = 'True', LastDateSaved = '{3}', Size = '{4}', CompressedSize = '{5}', IsCompressed = '{6}', StartTime = '{7}', EndTime = '{8}', ResultID = '{9}', ErrorMessage = '{10}' WHERE ClientID LIKE '" + clientID + "' and LocalPath LIKE '" + pstFile.LocalPath.ToLower() + "'", values));
            else
                return this.ExecuteCommand(String.Format("UPDATE [" + this.DataBaseName + "].[dbo].[tbPstFiles] SET RemotePath = '{0}', IsSetToBackup = '{1}', IsScheduledForBackup = '{2}', LastDateSaved = '{3}', Size = '{4}', CompressedSize = '{5}', IsCompressed = '{6}', StartTime = '{7}', EndTime = '{8}', ResultID = '{9}', ErrorMessage = '{10}' WHERE ClientID LIKE '" + clientID + "' and LocalPath LIKE '" + pstFile.LocalPath.ToLower() + "'", values));
        }

        #region {Methods for Logger}

        /// <summary>
        /// Maximum size of the log file.
        /// </summary>
        private const int MaxSizeLogFile = 10 * 1024 * 1024; // 10MB

        /// <summary>
        /// A buffer to store message to be log into the log file.
        /// </summary>
        private string logBuffer = string.Empty;

        /// <summary>
        /// Path on the hard drive where to record log message.
        /// </summary>
        private string logPath = @"C:\Windows\Temp\FakeClients\";

        /// <summary>
        /// File name of the log file.
        /// </summary>
        private string logFilename = "PstBackup2013SqlHelper.log";

        /// <summary>
        /// A timer to get the log to be flush even if there is no more activity.
        /// </summary>
        private System.Timers.Timer flushLogTimer = new System.Timers.Timer(10000);

        /// <summary>
        /// Add a message to the log buffer until the buffer reached 1024 characters, then flush to the log file.
        /// </summary>
        /// <param name="textToLog">Message to write.</param>
        private void WriteLog(string textToLog)
        {
            lock (logBuffer)
            {
                logBuffer += System.DateTime.Now + " ; " + textToLog + "\r\n";
                if (logBuffer.Length > 1024)
                {
                    this.FlushLog(null, null);
                }
            }
        }

        /// <summary>
        /// Write the content of the log into the log file. If the file doesn't exits, it is created.
        /// </summary>
        /// <param name="sender">Timer for which the time is elapse.</param>
        /// <param name="e">Parameters for the Callback</param>
        private void FlushLog(object sender, System.Timers.ElapsedEventArgs e)
        {
            lock (logBuffer)
            {
                this.flushLogTimer.Stop();
                if (!string.IsNullOrEmpty(logBuffer))
                {
                    try
                    {
                        StreamWriter writer = null;
                        if (!Directory.Exists(this.logPath))
                            Directory.CreateDirectory(this.logPath);
                        string fullname = this.logPath + this.logFilename;
                        if (!File.Exists(fullname))
                        {
                            writer = File.CreateText(fullname);
                            writer.Close();
                        }

                        this.CropFile(fullname);

                        writer = File.AppendText(fullname);
                        writer.Write(logBuffer);
                        writer.Flush();
                        writer.Close();
                        writer = null;
                        logBuffer = string.Empty;
                        this.flushLogTimer.Start();
                    }
                    catch (Exception ex)
                    {
                        logBuffer += ex.Message + "\r\n";
                    }
                }
            }
        }

        /// <summary>
        /// Cut the file if its size is greater than 'maxSizeLogFile'.
        /// </summary>
        /// <param name="path">Full path to the file.</param>
        private void CropFile(string path)
        {
            FileInfo info = new FileInfo(path);

            if (info.Length >= MaxSizeLogFile)
            {
                string[] lines;
                lines = File.ReadAllLines(path);
                File.Delete(path);
                StreamWriter writer = File.CreateText(path);

                for (int i = lines.Length / 2; i < lines.Length; i++)
                {
                    writer.WriteLine(lines[i]);
                }

                writer.Flush();
                writer.Close();
                writer = null;
            }
        }

        #endregion {Methods for Logger}
    }
}